Excel Training Manual for Financial Professionals by Anton Du Plessis

Author:Anton Du Plessis [Du Plessis, Anton]
Language: eng
Format: azw3
Publisher: UNKNOWN
Published: 2017-01-15T05:00:00+00:00

Exercise: PivotTables

Open file: PivotTables.xlsx

Steps

Select any cell within the data on the Sales worksheet Click on [Insert][Tables][PivotTable]

Click OK on the Create PivotTable dialogue box Select the applicable fields as follows:

Your results should be similar to:

Select the Value Field Settings… of the Amount Excl VAT field

Change the name to Sales and the number format to Accounting with no symbol Center align the Sales heading

Sort the Sales data from highest to lowest values

Then collapse the Regionheadings

Apply any PivotTable style to your PivotTable

Clickon[PivotTable Tools Options][Calculations][Fields, Items & Sets][Calculated Field] Name: Cost Price

Formula: ='Amount Excl VAT'/125*100 (Remember to double click on Amount Excl VAT to use it, don't retype it)

Format the heading the same as for Sales (Field name Cost Price, remember to add a space as this name already exist)

Clickon[PivotTable Tools Options][Fields, Items & Sets][Calculated Field] Name: Gross Profit

Formula: ='Amount Excl VAT' -'Cost Price' (Remember to double click on Amount Excl VAT and Cost Price to use it, don't retype it)

Format the heading the same as for Sales

Double click an amount in the Head Office line, you should get a new worksheet with only the Head Office data

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.